cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data"



/////////////////////////////////////////
// Combine loan-year dataset with assets data from bank regulatory
use ultimateparentid year lender using loan_year_dataset, clear

count
joinby ultimateparentid  using bank-assets, unm(both)
tab _m
keep if _m==3
drop _m

keep if  year(dofm(date))==year-1 | year(dofm(date))==year-2


bysort ultimateparentid year: egen max_date=max(date)
format max_date %tm

keep if date==max_date 
count

keep ultimateparentid year  total_assets has_deposits asset_growth_3

compress
bysort  ultimateparentid year: keep if _n==1

save matched-bank-data, replace


// matched dataset at the bank-year level

use  loan_year_dataset, clear
bysort  ultimateparentid year: keep if _n==1
joinby ultimateparentid year using matched-bank-data, unm(master)
tab _m
drop _m

// Add in mtr news (file done in 5-load-nexis)
joinby ultimateparentid year using article-bank-year, unm(master)
tab _m
drop _m
replace relevant_article=0 if mi(relevant_article)
label var relevant_article "News Articles"

// Add in mtr deposits (file done in 6-branch-location)
joinby ultimateparentid year using mtrdepositshare, unm(master)
tab _m
drop _m
replace mtrdepositshare=0 if mi(mtrdepositshare)
label var mtrdepositshare "Share of Deposits in MTR States"

// Add in ESG rankings (file done in 7-load-ESG)
joinby ultimateparentid year using esg-scores-cleaned.dta, unm(master)
tab _m
drop _m


compress
bysort  ultimateparentid year: keep if _n==1
save matched_dataset_annual, replace

exit
